﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;

namespace XKControlLib.ExportExcel
{
    public class ExcelToData
    {
        public static DataTable ToData()
        {
            try
            {
                var Fd = new OpenFileDialog();
                Fd.Filter = "Excel文件| *.xls;*.xlsx";
                if (Fd.ShowDialog() == DialogResult.OK)
                {
                    return ToData(Fd.FileName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return null;
        }

        private static DataTable ToData(string fileName)
        {
            DataTable dt = new DataTable();
            Type objClassType = Type.GetTypeFromProgID("Excel.Application");
            var objExcel = Activator.CreateInstance(objClassType);
            var objBooks = objExcel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objExcel, null);
            var objBook = objBooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, objBooks, new Object[] { fileName, 0, true });
            var objSheets = objBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook, null);
            var objSheet = objSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets, new Object[] { 1 });
            if( objSheet == null ){ return null; }

            var objUsedRange = objSheet.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, objSheet, null);
            var objRows = objUsedRange.GetType().InvokeMember("Rows", BindingFlags.GetProperty, null, objUsedRange, null);
            var objColumns = objUsedRange.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, objUsedRange, null);

            var objRowCount = objRows.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, objRows, null);
            var objColCount = objColumns.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, objColumns, null);
            var iRowCount = int.Parse(objRowCount.ToString());
            var iColCount = int.Parse(objColCount.ToString());
            var ColumnID = 1;
            var objRange = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { 1, 1 });
            while(iColCount >= ColumnID) { 
                var dc = new DataColumn();
                dc.DataType = Type.GetType("System.String");
                var objRangeValue = objRange.GetType().InvokeMember("Text", BindingFlags.GetProperty, null, objRange, null);
                var strNewColumnName = objRangeValue.ToString().Trim();
                if (strNewColumnName.Length == 0) { strNewColumnName = "_1"; }

                if( dt.Columns.Count > 0) {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if(dt.Columns[i].ColumnName == strNewColumnName)
                            {
                                strNewColumnName += "_1";
                            }
                        }
                }
                dc.ColumnName = strNewColumnName;
                dt.Columns.Add(dc);
                ColumnID += 1;
                objRange = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { 1, ColumnID });
            }
            for (int iRow = 2; iRow <= iRowCount; iRow++)
            {
                var dr = dt.NewRow();
                for (int iCol = 1; iCol <= iColCount; iCol++)
                {
                    objRange = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { iRow, iCol });
                    var objRangeValue = objRange.GetType().InvokeMember("Text", BindingFlags.GetProperty, null, objRange, null);
                    dr[iCol-1] = objRangeValue.ToString();
                }
                dt.Rows.Add(dr);
            }

            //objBook.GetType().InvokeMember("Save", BindingFlags.GetProperty, null, objBook, new Object[] {});
            objBook.GetType().InvokeMember("Close", BindingFlags.GetProperty, null, objBook, new Object[] { });
            objExcel.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, objExcel, new Object[] { false });
            objExcel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, objExcel, null);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            return dt;
        }
    }
}
